from django.http import HttpResponse
from django.shortcuts import render

from app4.models import App4UserBaseInfo, UserExtraInfo, CardInfo, SkillInfo
from app4.models import DepartInfo
import django.utils.timezone as timezone
from django.db.models import F,Q
from django.db import connection, transaction


# Create your views here.

# 新增操作
def add(request):
    # 第一种模式:save()
    depart = DepartInfo()
    depart.departname = '技术部'
    depart.createdate = timezone.now()
    depart.save()
    # 第一种模式:save()
    depart2 = DepartInfo(departname='网络部', createdate=timezone.now())
    depart2.save()

    # 第二种模式：create()
    d = dict(username="虞姬2",password='123456',status=1,createdate=timezone.now())
    user = App4UserBaseInfo.objects.create(**d)
    # 第二种模式：create()
    user2 = App4UserBaseInfo.objects.create(username="貂蝉2",password='123456',status=0,createdate=timezone.now())
    return HttpResponse('新增成功')

# 更新操作
def update(request):
    # 第一种：save方法
    try:
        one_user = App4UserBaseInfo.objects.get(id=1)
        one_user.username = "刘备3"
        one_user.save()
    except App4UserBaseInfo.DoesNotExist:
        return HttpResponse("用户不存在")
    # 第二种：update方法
    one_user = App4UserBaseInfo.objects.filter(id=2).update(username="李白3")
    print(one_user)
    # 批量更新
    users = App4UserBaseInfo.objects.update(status=1) # 返回整数（受影响的行数）
    print(users)
    return HttpResponse("更新成功"+str(users))

# 删除操作
def delete(request):
    # 如果删除的数据有外键字段，且模型中的on_delete参数被设置为CASCADE，则删除外键关联表中的对应数据
    # 删除单行数据
    try:
        oneuser = App4UserBaseInfo.objects.get(id=7)
        result = oneuser.delete() # 返回删除数据的行数信息
        print(result)
    except App4UserBaseInfo.DoesNotExist:
        return HttpResponse("要删除的数据不存在")
    # 删除多行数据
    result2 = App4UserBaseInfo.objects.filter(status=0).delete()
    print(result2)
    # 删除全部数据
    result3 = App4UserBaseInfo.objects.all().delete()
    print(result3)
    return HttpResponse("删除成功")

# 一对一关联表查询操作
def oneToOne(request):
    # 添加用户基本信息表
    userBaseInfoDict = dict(username='马超',password='123456',status=1,createdate=timezone.now())
    one_user = App4UserBaseInfo.objects.create(**userBaseInfoDict)
    print(one_user)
    # 添加部门信息
    tech_department,_ = DepartInfo.objects.get_or_create(departname='技术部', defaults={'createdate':timezone.now()})
    print(tech_department)
    # 上面一行代码可以用下面的代码取代
    # try:
    #     # 尝试获取现有对象
    #     tech_depart = DepartInfo.objects.get(departname='技术部')
    # except DepartInfo.DoesNotExist:
    #     # 不存在则创建新对象
    #     tech_depart = DepartInfo.objects.create(
    #         departname='技术部',
    #         createdate=timezone.now()
    #     )
    # 添加用户扩展信息表
    userExtraInfoDict = dict(username='马超',truename='马小超',sex=0,salary=6000.88,age=20,status=1
                             ,createdate=timezone.now(),memo='',user=one_user,depart=tech_department)
    extraInfo = UserExtraInfo.objects.create(**userExtraInfoDict)
    print(extraInfo)

    #通过基本表访问扩展表
    try:
        user = App4UserBaseInfo.objects.get(id=one_user.id)
        print(user.userextrainfo.truename) # 扩展表名称小写
    except App4UserBaseInfo.DoesNotExist:
        return HttpResponse("用户不存在")
    #通过扩展表访问基本表
    try:
        extra = UserExtraInfo.objects.get(id=extraInfo.id)
        print(extra.user.username) # 外键操作
    except UserExtraInfo.DoesNotExist:
        return HttpResponse("用户不存在")
    return HttpResponse("一对一关联成功")

# 一对多关联操作
def oneToMany(request):
    base_user = App4UserBaseInfo.objects.get(id=1)
    # 判断是用户11的卡片信息是否为空，为空则不新增，否则信息
    user_cards = CardInfo.objects.filter(user_id=base_user.id)
    if user_cards.count() == 0:
        # 添加卡片信息
        card1 = CardInfo(cardno='11111111111111',bank='工商银行',user=base_user)
        card1.save()
        card2 = CardInfo(cardno='22222222222222',bank='中国银行',user=base_user)
        card2.save()
        print('新增用户卡片信息成功')
    # 从一查询多
    #cards = base_user.cardinfo_set.all() # 模型名称小写+_set
    # 也可以用模型中配置的related_name名称进行查询
    # 当设置了 related_name 时，Django 不会创建默认的反向关系管理器
    # 默认的反向关系管理器名称是 小写模型名_set（即 cardinfo_set）
    # 但你指定了自定义名称 usercard，所以只能使用这个名称进行反向查询
    cards = base_user.usercard.all()
    print(cards)
    for card in cards:
        print(card.bank+card.cardno)
    # 从多查询一
    card = CardInfo.objects.get(id=1)
    print(card.user.username) # 通过外键操作
    return HttpResponse("一对多成功")

# 多对对关联
def manyToMany(request):
    skills = SkillInfo.objects.all()
    if skills.count() == 0:
        skill1 = SkillInfo(skillname='编程',createdate=timezone.now())
        skill1.save()
        skill2 = dict(skillname='舞蹈',createdate=timezone.now())
        SkillInfo.objects.create(**skill2)
        skill3 = dict(skillname='画画',createdate=timezone.now())
        SkillInfo.objects.create(**skill3)
        print('新增技能成功')

    # 给所有用户增加 id=1 的技能
    user_all = UserExtraInfo.objects.all()
    skill = SkillInfo.objects.get(id=1)
    result = skill.user.add(*user_all)
    print('给所有用户增加技能id为1的技能')
    print(result)

    # 通过扩展用户信息表访问技能表
    print('通过扩展用户信息表访问技能表')
    user_11 = UserExtraInfo.objects.get(id=3)
    result2 = user_11.skillinfo_set.all()
    print(result2)

    # 通过技能表访问扩展用户信息表
    print('通过技能表访问扩展用户信息表')
    skill_1 = SkillInfo.objects.get(id=1)
    result3 = skill_1.user.all().get(id=3).username # 通过外键访问
    print(result3)

    return HttpResponse("多对多查询成功")

def manySet(request):
    users = UserExtraInfo.objects.all()
    skill = SkillInfo.objects.get(id=2)
    # 修改全部用户的技能为2
    result = skill.user.set(users)
    print(result)
    users = [3,4]
    result2 = skill.user.set(users) # 会删除用户id为3,4之外的关联关系
    print(result2)
    return HttpResponse("修改关联数据")

def remove(request):
    user = App4UserBaseInfo.objects.get(id=3)
    # 新增技能
    # if not SkillInfo.objects.filter(id=1).exists() :
    #     skill = SkillInfo(skillname='轮滑',createdate=timezone.now())
    #     skill.save()
    #     skill1 = skill
    # else:
    #     skill1 = SkillInfo.objects.get(id=1)
    # 下面的写法等于上面的：不存在则创建
    skill1,created = SkillInfo.objects.get_or_create(id=1,defaults={
        'skillname' : '轮滑', 'createdate' : timezone.now()
    })
    depart,created = DepartInfo.objects.get_or_create(id=1,defaults={
        'departname': '技术部', 'createdate' : timezone.now()
    })
    extra,created = UserExtraInfo.objects.get_or_create(id=1,defaults={
        'username' : '马超', 'truename' : '马小超', 'sex' : 0, 'salary' : 6000.88, 'age' : 20, 'status' : 1
    , 'createdate' : timezone.now(), 'memo' : '', 'user' : user, 'depart' : depart
    })
    # 将技能id为1的技能关联到用户id为1的用户上
    skill1.user.add(extra)
    # 删除指定的用户
    result = skill1.user.remove(extra) # 删除指定的用户实例
    print(result)
    result3 = skill1.user.remove(1) # 删除莫格用户id
    print(result3)
    result2 = skill1.user.clear() # 删除skill_id为1的全部用户
    print(result2)

    return HttpResponse("删除成功")

def select_related(request):
    # 下面的写法，首先查询Cardinfo表，在循环获取用户信息
    # cards = CardInfo.objects.all()
    # for card in cards:
    #     print(card.user.username)
    # select_related 只查询一次数据库，进行关联查询。并且只能用在一对多外键属性中
    cards = CardInfo.objects.select_related('user')
    for card in cards:
        print(card.user.username)
    return HttpResponse("查询成")

# 关联查询用于解决多对一或多对多，减少查询次数
def prefetch_related(request):
    skill1,created = SkillInfo.objects.get_or_create(skillname='编程',defaults={
        'skillname' : '编程', 'createdate' : timezone.now()
    })
    skill2, created = SkillInfo.objects.get_or_create(skillname='舞蹈', defaults={
        'skillname': '舞蹈', 'createdate': timezone.now()
    })
    skill3, created = SkillInfo.objects.get_or_create(skillname='画画', defaults={
        'skillname': '画画', 'createdate': timezone.now()
    })
    depart, created = DepartInfo.objects.get_or_create(id=1, defaults={
        'departname': '技术部', 'createdate': timezone.now()
    })
    userinfo = App4UserBaseInfo.objects.get(id=1)
    extra1,created = UserExtraInfo.objects.get_or_create(username='梦琪',defaults={
        'username': '梦琪', 'truename': '梦小琪', 'sex': 0, 'salary': 6000.88, 'age': 20, 'status': 1
        , 'createdate': timezone.now(), 'memo': '', 'user': userinfo, 'depart': depart
    })
    userinfo = App4UserBaseInfo.objects.get(id=2)
    extra2, created = UserExtraInfo.objects.get_or_create(username='藏非', defaults={
        'username': '藏非', 'truename': '藏小非', 'sex': 0, 'salary': 6000.88, 'age': 20, 'status': 1
        , 'createdate': timezone.now(), 'memo': '', 'user': userinfo, 'depart': depart
    })
    skill1.user.add(extra1)
    skill2.user.add(extra1)
    skill3.user.add(extra2)

    skills = SkillInfo.objects.prefetch_related('user')
    for skill in skills:
        print(skill.skillname)
        users = skill.user.all()
        for user in users:
            print(user.username)
    return HttpResponse("多对的关联查询")

# 使用F（）函数可以直接在数据库操作，可以减少某些操作所需要的数据库查询次数
def functionF(request):
    users = UserExtraInfo.objects.all()
    if users.count() == 0:
        return HttpResponse("用户为空")
    for user in users:
        user.salary = F('salary') + 1000 # 内存中生成表达式：F(salary) + Value(1000)
        user.save() # 更新数据库的值
        print("执行refresh_from_db前")
        print(user.username + str(user.salary))
        print("执行refresh_from_db后")
        # 主动从数据库同步最新值到内存中
        user.refresh_from_db() # 如果无需立即使用对象，可以不使用refresh_from_db
        print(user.username + str(user.salary))
    return HttpResponse("F函数使用成功")

# Q函数用于对象多条件查询
def functionQ(request):
    users = UserExtraInfo.objects.filter(Q(age__gt=30)&Q(salary__gt=5000))
    if users.count() != 0:
        for user in users:
            print(user.username)
    return HttpResponse("q函数正常执行")

# raw函数用来执行自定义SQL
def raw(request):
    users = UserExtraInfo.objects.raw("select * from userbaseinfo4")
    for user in users:
        print(type(user),user)

    # 条件查询
    name = '马超3'
    sql = '''
            select * from userbaseinfo4 where username=%s
            '''
    users = UserExtraInfo.objects.raw(sql, (name,))
    for user in users:
        print(user.id)
     # 多表查询
    sql = '''
        select a.*,b.* from userbaseinfo4 a,app4_userextrainfo b where a.id = b.user_id'''
    users = App4UserBaseInfo.objects.raw(sql)
    for user in users:
        print(user.username)
    return HttpResponse("raw方法正常执行")


# 游标函数cursor：是系统为用户开设的一个数据缓冲区，主要用来处理和接收数据，如对数据库的增加，删除，修改和查询等操作
def functionCursor(request):
    # 插入数据
    cursor = connection.cursor()
    insert_sql = 'insert into app4_departinfo(departname,createdate) values(%s,%s)'
    data = ('总经办',timezone.now())
    cursor.execute(insert_sql, data)
    cursor.close()

    # 查询数据
    cursor = connection.cursor()
    cursor.execute('select * from app4_departinfo')
    row = cursor.fetchone() # 以元组的方式返回一条记录
    print(row)
    cursor.close()

    # 更新数据
    try:
        # cursor = connection.cursor()
        # update_sql = 'update app4_departinfo set departname = %s where id = %s'
        # data = ('销售部',1)
        # cursor.execute(update_sql, data)
        # rowcount  = cursor.rowcount # 影响的行数
        # print('更新：' + str(rowcount))
        # cursor.close()
        # with语法自动关闭游标，不需要显示关闭
        with connection.cursor() as cursor:
            update_sql = 'update app4_departinfo set departname = %s where id = %s'
            data = ('销售部', 1)
            cursor.execute(update_sql, data)
            rowcount = cursor.rowcount  # 影响的行数
            print(f'更新：{rowcount}')
        # 注意：Django默认自动提交，但显式提交更明确
        connection.commit()
    except Exception as e:
        connection.rollback()
        print(f'数据库错误：{e}')

    # 删除数据
    cursor = connection.cursor()
    sql = 'delete from app4_departinfo where id = %s'
    data = (3,)
    cursor.execute(sql, data)
    cursor.close()
    return HttpResponse('游标函数执行')

# 数据库事务管理: 装饰器方式定义事务
@transaction.atomic()  # 装饰器
def funTransaction(request):
    # 开启事务
    save_id = transaction.savepoint()
    try:
        # 保存基本信息
        d = dict(username='曹操',password='123456',status=1,createdate=timezone.now())
        userbaseinfo = App4UserBaseInfo.objects.create(**d)
        #raise # 模拟手动抛出异常
        d = dict(departname='人事部',createdate=timezone.now())
        department = DepartInfo.objects.create(**d)
        d = dict(username='曹操',truename='曹孟德',sex=0,salary=2000.66,age=59,status=1
                             ,createdate=timezone.now(),memo='测试',user=userbaseinfo,depart=department)
        extrauser = UserExtraInfo.objects.create(**d)
        transaction.savepoint_commit(save_id) # 提交从保存点到当前状态的所有数据库事务操作
    except Exception as e:
        transaction.savepoint_rollback(save_id) # 事务回滚，回滚到保存点
        print(f'新增数据失败，失败原因{e}')
        return (f'新增数据失败，失败原因{e}')
    # with transaction.atomic(): # with方式定义事务
    #     # 开启事务
    #     save_id = transaction.savepoint()
    #     try:
    #         # 代码操作1
    #         # 代码操作2
    #         transaction.savepoint_commit(save_id)
    #     except Exception as e:
    #         transaction.savepoint_rollback(save_id)
    return HttpResponse("新增数据成功")